﻿Imports System.Data

Partial Class MemberPages_Organization
    Inherits System.Web.UI.Page


    Dim conString As New OleDb.OleDbConnection
    Dim dataSet As New DataSet
    Dim dataAdapter As New OleDb.OleDbDataAdapter

    Dim dbProvider As String
    Dim dbSource As String
    Dim dbSource2 As String
    Dim sqlCommand As String
    Dim MaxRows As Integer
    Dim rows As Integer


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim UserLogged = Membership.GetUser.UserName

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"

        dbSource = "Data Source = "
        dbSource2 = Server.MapPath("~/Database/ImagineCup.mdb")

        'connection string to the global db, ImagineCup.
        conString.ConnectionString = dbProvider & dbSource & dbSource2

        conString.Open()

        'sql command to match logged user with an already existing foundation in the database.
        sqlCommand = "SELECT * FROM ImagineCup_Org WHERE UserName= '" & UserLogged & "'"
        dataAdapter = New OleDb.OleDbDataAdapter(sqlCommand, conString)
        dataAdapter.Fill(dataSet, "OrganizationTbl")

        conString.Close()

        'Get all rows in the organization table and assign it to the dataset.
        MaxRows = dataSet.Tables("OrganizationTbl").Rows.Count
        rows = MaxRows - 1

        If rows < 0 Then

            lblOrganizationName.Text = ("You have not created an organization page yet.")

        Else

            lblOrganizationName.Text = dataSet.Tables("OrganizationTbl").Rows(rows).Item("org_Name")
            lblFounder.Text = dataSet.Tables("OrganizationTbl").Rows(rows).Item("Founder")
            lblDateEstablished.Text = dataSet.Tables("OrganizationTbl").Rows(rows).Item("est_Date")

        End If

        'if conditions are met, show controls.
        ShowControls()
        showInfo()
        lbluserName.Text = UserLogged

    End Sub

    Protected Sub cmdDeleteOrg_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdDeleteOrg.Click

        If rows < 0 Then

            lblOutput.Text = ("You cannot delete a non-existing database!")

        Else

            Dim UserLogged = Membership.GetUser.UserName

            Dim sqlDelete As String = "DELETE * FROM ImagineCup_Org WHERE UserName= '" & UserLogged & "'"

            Dim sqlDeleteQuery As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlDelete, conString)

            conString.Open()
            sqlDeleteQuery.ExecuteNonQuery()
            conString.Close()

            dataAdapter.Update(dataSet, "OrganizationTbl")

        End If

    End Sub

    Public Sub ShowControls()

        If rows < 0 Then

            lblFounder.Enabled = False

            lblFounder.Visible = False

            lblDateEstablished.Enabled = False

            lblDateEstablished.Visible = False

            cmdDeleteOrg.Enabled = False

            cmdDeleteOrg.Visible = False

            cmdAddP.Enabled = False

            cmdAddP.Visible = False

            cmdHelp.Enabled = False

            cmdHelp.Visible = False

            cmdInfo.Enabled = False

            cmdInfo.Visible = False

            GridView1.Enabled = False

            GridView1.Visible = False

            lblRetrievedInfo.Enabled = False

            lblRetrievedInfo.Visible = False

            txtaddLocation.Visible = False

            txtaddName.Visible = False

            txtaddTypeHelp.Visible = False

            txtaddWhoToContact.Visible = False

            txthelpName.Visible = False

            txthelpType.Visible = False

            btnSearch.Visible = False

            selectionWhereList.Visible = False

        Else


            lblFounder.Enabled = True

            lblFounder.Visible = True

            lblDateEstablished.Enabled = True

            lblDateEstablished.Visible = True

            cmdDeleteOrg.Enabled = True

            cmdDeleteOrg.Visible = True

            cmdAddP.Enabled = True

            cmdAddP.Visible = True

            cmdHelp.Enabled = True

            cmdHelp.Visible = True

            cmdInfo.Enabled = True

            cmdInfo.Visible = True

            GridView1.Enabled = True

            GridView1.Visible = True

            lblRetrievedInfo.Enabled = True

            lblRetrievedInfo.Visible = True

            txtaddLocation.Visible = True

            txtaddName.Visible = True

            txtaddTypeHelp.Visible = True

            txtaddWhoToContact.Visible = True

            txthelpName.Visible = True

            txthelpType.Visible = True

            btnSearch.Visible = True

            selectionWhereList.Visible = True

        End If

    End Sub

    Public Sub showInfo()

        Dim sqlFind As String
        Dim sqlFind2 As String

        conString.Open()

        sqlFind = "SELECT * FROM ImagineCup_PeopleHelped WHERE by_Who= '" & lblOrganizationName.Text & "'"

        dataAdapter = New OleDb.OleDbDataAdapter(sqlFind, conString)
        dataAdapter.Fill(dataSet, "PeopleHelped")

        conString.Close()

        MaxRows = dataSet.Tables("PeopleHelped").Rows.Count

        lblPeopleHelped.Text = MaxRows

        conString.Open()

        sqlFind2 = "SELECT * FROM ImagineCup_PeopleInNeed"

        dataAdapter = New OleDb.OleDbDataAdapter(sqlFind2, conString)

        dataAdapter.Fill(dataSet, "PeopleInNeed")

        conString.Close()

        Dim rows_PeopleInNeed As Integer

        rows_PeopleInNeed = dataSet.Tables("PeopleInNeed").Rows.Count

        lblPeopleInNeed.Text = rows_PeopleInNeed


    End Sub

    Protected Sub cmdAddP_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdAddP.Click

        Dim dataRow As DataRow
        Dim commandString As String

        On Error Resume Next
        commandString = "INSERT INTO ImagineCup_PeopleInNeed (person_Name, Location, help_Type, contact_ToLocate) VALUES (" + "'" + txtaddName.Text + "'," + "'" + txtaddLocation.Text + "'," + "'" + txtaddTypeHelp.Text + "'," + "'" + txtaddWhoToContact.Text + "'" + ")"

        Dim sqlInsert As OleDb.OleDbCommand = New OleDb.OleDbCommand(commandString, conString)

        dataRow = dataSet.Tables("PeopleInNeed").NewRow()

        dataSet.Tables("PeopleInNeed").Rows.Add(dataRow)

        conString.Open()
        sqlInsert.ExecuteNonQuery()
        conString.Close()

        lblOutput.Text = "Person succesfully added!"

        Response.AddHeader("Refresh", "1")

    End Sub

    Protected Sub cmdHelp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdHelp.Click

        Dim sqlFind As String

        conString.Open()

        sqlFind = "SELECT * FROM ImagineCup_PeopleInNeed WHERE person_Name= '" & txthelpName.Text & "'"

        dataAdapter = New OleDb.OleDbDataAdapter(sqlFind, conString)
        dataAdapter.Fill(dataSet, "PeopleInNeed")

        conString.Close()

        MaxRows = dataSet.Tables("PeopleInNeed").Rows.Count

        lblhelpWhoToContact.Text = dataSet.Tables("PeopleInNeed").Rows(MaxRows - 1).Item("contact_ToLocate")

    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        On Error Resume Next

        If selectionWhereList.SelectedItem.Text = "Organizations Table" Then

            Dim sqlFind As String
            Dim addressString As String
            Dim contactPersonString As String
            Dim telephoneNumberString As String
            Dim faxNumberString As String
            Dim locationString As String
            Dim founderString As String
            Dim emailString As String
            Dim usernameString As String
            Dim est_DateString As String

            conString.Open()

            sqlFind = "SELECT * FROM ImagineCup_Org"

            dataAdapter = New OleDb.OleDbDataAdapter(sqlFind, conString)
            dataAdapter.Fill(dataSet, "OrganizationTbl")

            conString.Close()

            MaxRows = dataSet.Tables("OrganizationTbl").Rows.Count

            addressString = "Address: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("Address")
            contactPersonString = ", Contact Person: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("contact_Person")
            telephoneNumberString = ", Telephone Number: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("telephone_Number")
            faxNumberString = ", Fax Number: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("fax_Number")
            locationString = ", Location: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("Location")
            founderString = ", Founder: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("Founder")
            emailString = ", Email: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("Email")
            usernameString = ", Username: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("userName")
            est_DateString = ", Date est.: " + dataSet.Tables("OrganizationTbl").Rows(MaxRows - 1).Item("est_Date")

            lblRetrievedInfo.Text = addressString + contactPersonString + telephoneNumberString + faxNumberString + locationString + founderString + emailString + est_DateString

        ElseIf selectionWhereList.SelectedItem.Text = "People Helped Table" Then

            Dim sqlFind As String
            Dim personNameString As String
            Dim bywhoString As String
            Dim whenString As String
            Dim typehelpString As String
            Dim outputString As String

            conString.Open()

            sqlFind = "SELECT * FROM ImagineCup_PeopleHelped"

            dataAdapter = New OleDb.OleDbDataAdapter(sqlFind, conString)
            dataAdapter.Fill(dataSet, "PeopleHelped")

            conString.Close()

            MaxRows = dataSet.Tables("PeopleHelped").Rows.Count

            personNameString = "Name: " + dataSet.Tables("PeopleHelped").Rows(MaxRows - 1).Item("person_Name")
            bywhoString = ", Organization that helped: " + dataSet.Tables("PeopleHelped").Rows(MaxRows - 1).Item("by_Who")
            whenString = ", Date: " + dataSet.Tables("PeopleHelped").Rows(MaxRows - 1).Item("when")
            typehelpString = ", Type of help provided: " + dataSet.Tables("PeopleHelped").Rows(MaxRows - 1).Item("type_Help")

            outputString = personNameString + bywhoString + whenString + typehelpString

            lblRetrievedInfo.Text = outputString

        ElseIf selectionWhereList.SelectedItem.Text = "People In Need Table" Then

            Dim sqlFind As String
            Dim personNameString As String
            Dim locationString As String
            Dim helptypeString As String
            Dim whotocontactString As String
            Dim outputString As String

            conString.Open()

            sqlFind = "SELECT * FROM ImagineCup_PeopleInNeed"

            dataAdapter = New OleDb.OleDbDataAdapter(sqlFind, conString)
            dataAdapter.Fill(dataSet, "PeopleInNeed")

            conString.Close()

            MaxRows = dataSet.Tables("PeopleInNeed").Rows.Count

            personNameString = "Name: " + dataSet.Tables("PeopleInNeed").Rows(MaxRows - 1).Item("person_Name")
            locationString = ", Location: " + dataSet.Tables("PeopleInNeed").Rows(MaxRows - 1).Item("Location")
            helptypeString = ", Help type needed: " + dataSet.Tables("PeopleInNeed").Rows(MaxRows - 1).Item("help_Type")
            whotocontactString = ", Who to get in contact with to locate person: " + dataSet.Tables("PeopleInNeed").Rows(MaxRows - 1).Item("contact_ToLocate")

            outputString = personNameString + locationString + helptypeString + whotocontactString

            lblRetrievedInfo.Text = outputString

        End If


    End Sub
End Class